/*
 * SalesBatchHandler.java
 *
 * Created on March 28, 2007, 11:11 PM
 *
 * To change this template, choose Tools | Template Manager
 * and open the template in the editor.
 */

package edu.byu.isys413.group1E;

import edu.byu.isys413.group1E.data.*;
import java.sql.*;

/**
 *
 * @author Kendall Frazier
 */
public class SalesBatchHandler {
    
    private static SalesBatchHandler instance = null;
    
    /** Creates a new instance of SalesBatchHandler */
    public SalesBatchHandler() {
    }
    
    /**
     * 
     * @return 
     */
    public static synchronized SalesBatchHandler getInstance() {
        if (instance == null) {
            instance = new SalesBatchHandler();
        }
        return instance;
    }
    
    /**
     * 
     * @throws java.lang.Exception 
     */
    public void PreparePurchaseOrders()throws Exception{
        PurchaseOrderDAO PODAO = PurchaseOrderDAO.getInstance();
        OrderLineDAO OLDAO = OrderLineDAO.getInstance();
        VendorDAO vendDAO = VendorDAO.getInstance();
        VendorItemDAO vendorItemDAO = VendorItemDAO.getInstance();
        ConceptualDAO conceptualDAO = ConceptualDAO.getInstance();
        StoreProductDAO storeProductDAO = StoreProductDAO.getInstance();
        
        PurchaseOrderBO POBO = PODAO.create();
        
                
        Connection conn = ConnectionPool.getInstance().get();
        conn.setAutoCommit(false);
        
        Statement stmt1 = conn.createStatement();
        Statement stmt2 = conn.createStatement();
        ResultSet rs = stmt1.executeQuery("SELECT * FROM Store_Product");        
        
        //build the object from the result set, see if it needs to be reordered, and reorder it!
        while(rs.next()) {
            StoreProductBO storeProductBO = storeProductDAO.create(rs.getString("storeID"), rs.getString("productID"));
            
            storeProductBO.setQtyOnHand(rs.getInt("QtyOnHand"));
            storeProductBO.setQtyOnOrder(rs.getInt("qtyOnOrder"));
            storeProductBO.setQtyToOrder(rs.getInt("qtyToOrder"));
            storeProductBO.setReorderPoint(rs.getInt("reorderPoint"));
            storeProductBO.setShelfLocation(rs.getString("shelfLocation"));    
            
            //if we need more of this item
            if(storeProductBO.getQtyOnHand() < storeProductBO.getReorderPoint() && storeProductBO.getQtyOnOrder()+storeProductBO.getQtyOnHand() < storeProductBO.getReorderPoint()){
                
                //find the lowest bidder
                System.out.println("CONCEPTUALID for the RS2 querry: "+storeProductBO.getConceptualID());
                ResultSet rs2 = stmt2.executeQuery("SELECT * FROM Vendor_Product WHERE ProductID = '" + storeProductBO.getConceptualID() + "'");
                
                String winnerID = null;
                double winner = 999999999;
                double newContendor;
                while(rs2.next()) {
                    newContendor = rs2.getDouble("cost");
                    if(winner > newContendor){
                        winner = newContendor;
                        winnerID = rs2.getString("vendorID");
                    }//end if                  
                }//end finding the winner
                
                //add to qty to the store_product
                storeProductBO.setQtyOnOrder(storeProductBO.getQtyOnOrder()+storeProductBO.getQtyToOrder());
                
                //create an order-line for that product
                OrderLineBO OLBO = OLDAO.create();
                OLBO.setConceptual((ConceptualBO)conceptualDAO.read(storeProductBO.getConceptualID(), conn));
                OLBO.setQuantity(storeProductBO.getQtyToOrder());
                OLBO.setLineAmount(storeProductBO.getQtyToOrder()*vendorItemDAO.read(winnerID, storeProductBO.getConceptualID(), conn).getCost());
                
                //add the order-line to the Purchase Order            
                OLBO.setPOID(POBO.getPOID());
                POBO.addOrderLine(OLBO);
                POBO.setVendor(vendDAO.read(winnerID, conn));
                
            }//end if we need more of this store_product            
        }// end loop of all store_products
        
        POBO.calcTotal();
        
        System.out.println("PO: the ID is: "+POBO.getPOID());
        System.out.println("PO: the total due is: "+POBO.getPOTotal());
        System.out.println("PO: there are "+POBO.getOrderLineList().size()+" order lines");
        for(int counter = 0; counter < POBO.getOrderLineList().size(); counter ++){
            System.out.println("PO: the OrderLine's Product's Description is: "+POBO.getOrderLineList().get(counter).getConceptual().getDescription());
            System.out.println("PO: the OrderLine's quantity is: " + POBO.getOrderLineList().get(counter).getQuantity());
            System.out.println("PO: the OrderLine's total cost is: "+POBO.getOrderLineList().get(counter).getLineAmount());
        }//end forloop of orderLines
        
        stmt1.close();
        stmt2.close();
        ConnectionPool.getInstance().release(conn);
    }//end 
}//end sale Handler
